﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2013/2/18 星期一 23:54:25
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Sector7.Framework.Core;
using Sector7.Framework.Enums;
using System.Collections;
using Sector7.Framework.Utility;
using System.Configuration;
using Sector7.Framework.Consts;

namespace Sector7.Framework.DB
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class DBHelper : IDisposable
    {
        #region Const members
        /// <summary>
        /// 未执行
        /// </summary>
        private const int NOT_YET = -1;

        /// <summary>
        /// 失败
        /// </summary>
        private const int FAILURE = 0;

        /// <summary>
        /// 成功
        /// </summary>
        private const int SUCCESS = 1;
        #endregion

        #region Inner members
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string _connectionString = string.Empty;

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private SqlConnection _connection = null;

        /// <summary>
        /// 数据库命令对象
        /// </summary>
        private SqlCommand _command = null;

        /// <summary>
        /// 数据库事物对象
        /// </summary>
        private SqlTransaction _transaction = null;

        /// <summary>
        /// 数据库只读流对象
        /// </summary>
        private SqlDataReader _reader = null;
        #endregion

        #region Accessor
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }
        #endregion

        #region Structure
        /// <summary>
        /// 创建数据库操作类对象
        /// </summary>
        public DBHelper()
        {
            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Sector7"].ConnectionString;
            InitializeConnection();
        }

        /// <summary>
        /// 创建数据库操作类对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public DBHelper(string connectionString)
        {
            _connectionString = connectionString;
            InitializeConnection();
        }
        #endregion

        #region Public functions
        /// <summary>
        /// 执行存储过程不返回数据集
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public int ExecuteStoredProcedureNonQuery(string storedProcedureName, params SqlParameter[] values)
        {
            int result = NOT_YET;

            try
            {
                OpenConnection();

                CreateCommand();

                CreateTransaction();

                _command.CommandText = storedProcedureName;
                _command.CommandType = CommandType.StoredProcedure;
                _command.Parameters.AddRange(values);

                Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                result = _command.ExecuteNonQuery();

                CommitTransaction();

                result = SUCCESS;
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);

                RollbackTransaction();

                result = FAILURE;
            }

            return result;
        }

        /// <summary>
        /// 执行存储过程并返回第一列
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public object ExecuteStoredProcedureScalar(string storedProcedureName, params SqlParameter[] values)
        {
            object scalar = null;

            try
            {
                OpenConnection();

                CreateCommand();

                CreateTransaction();

                _command.CommandText = storedProcedureName;
                _command.CommandType = CommandType.StoredProcedure;
                _command.Parameters.AddRange(values);

                Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                scalar = _command.ExecuteScalar();

                CommitTransaction();
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);

                RollbackTransaction();
            }

            return scalar;
        }

        /// <summary>
        /// 执行操作不返回数据集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] values)
        {
            return ExecuteNonQuery(sql, CommandType.Text, values);
        }

        /// <summary>
        /// 执行操作不返回数据集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] values)
        {
            int result = NOT_YET;

            try
            {
                OpenConnection();

                CreateCommand();

                CreateTransaction();

                _command.CommandText = sql;
                _command.CommandType = commandType;
                _command.Parameters.AddRange(values);

                Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                result = _command.ExecuteNonQuery();

                CommitTransaction();

                result = SUCCESS;
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);

                RollbackTransaction();

                result = FAILURE;
            }

            return result;
        }

        /// <summary>
        /// 执行数据批量操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="datas">数据对象列表</param>
        /// <param name="propertyNames">待插入属性名</param>
        /// <returns></returns>
        public int ExecuteBatchNonQuery(string sql, IEnumerable datas, params string[] propertyNames)
        {
            int result = FAILURE;

            try
            {
                OpenConnection();

                CreateCommand();

                CreateTransaction();

                _command.CommandText = sql;
                foreach (object data in datas)
                {
                    _command.Parameters.Clear();
                    foreach (string propertyName in propertyNames)
                    {
                        _command.Parameters.Add(DBUtil.CreateSqlParameter(ReflectionUtil.GetPropertyValue(data, propertyName), propertyName));
                    }

                    Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                    _command.ExecuteNonQuery();
                }

                CommitTransaction();

                result = SUCCESS;
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);

                RollbackTransaction();

                result = FAILURE;
            }

            return result;
        }

        /// <summary>
        /// 执行操作并返回第一列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params SqlParameter[] values)
        {
            object scalar = null;

            try
            {
                OpenConnection();

                CreateCommand();

                _command.CommandText = sql;
                _command.Parameters.AddRange(values);

                Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                scalar = _command.ExecuteScalar();
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);
            }

            return scalar;
        }

        /// <summary>
        /// 执行操作返回数据集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="values">预置参数</param>
        /// <returns></returns>
        public SqlDataReader ExecuteQuery(string sql, params SqlParameter[] values)
        {
            try
            {
                OpenConnection();

                CreateCommand();

                _command.CommandText = sql;
                _command.Parameters.AddRange(values);

                Log.WriteLine(LogSeverityType.DBG, C_ModuleName.FRAMEWORK, _command.CommandText);

                _reader = _command.ExecuteReader();

                return _reader;
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);

                throw e;
            }
        }

        /// <summary>
        /// 提交事物
        /// </summary>
        private void CommitTransaction()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
            }
        }

        /// <summary>
        /// 事物回滚
        /// </summary>
        private void RollbackTransaction()
        {
            if (_transaction != null)
                _transaction.Rollback();
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            GCUtil.Close(_reader);
            GCUtil.Dispose(_transaction);
            GCUtil.Dispose(_command);
            GCUtil.Close(_connection);
        }
        #endregion

        #region Private functions
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        private void InitializeConnection()
        {            
            _connection = new SqlConnection(_connectionString);
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        private void OpenConnection()
        {
            if (_connection != null && _connection.State != ConnectionState.Open)
                _connection.Open();
        }

        /// <summary>
        /// 创建数据库命令类
        /// </summary>
        private void CreateCommand()
        {
            if (_command == null)
                _command = _connection.CreateCommand();

            _command.Parameters.Clear();
        }

        /// <summary>
        /// 创建数据库事物
        /// </summary>
        private void CreateTransaction()
        {
            if (_transaction == null)
            {
                _transaction = _connection.BeginTransaction(IsolationLevel.Serializable);
                _command.Transaction = _transaction;
            }
        }        
        #endregion
    }
}
